LiveSurfaceFixedGrid
This table contains a live grided (interpolated) censored implied volatility surface. Each record contains standarized live and prior period implied volatilities at standarized skew points for a standardized days-to-expiration value.\nLiveSurfaceGrid records are published to the SpiderRock elastic cluster nightly.
METADATA
| Attribute | Value |
|---|---|
| Topic | 1000-analytics |
| MLink Token | OptSurface |
| Product | SRAnalytics |
| accessType | SELECT |
Table Definition
| Field | Type | Key | Default Value | Comment |
|---|---|---|---|---|
| ticker_at | enum - AssetType | PRI | 'None' | |
| ticker_ts | enum - TickerSrc | PRI | 'None' | |
| ticker_tk | VARCHAR(12) | PRI | '' | |
| days | SMALLINT | PRI | 0 | days to expiration 5 21 42 63 84 105 126 189 252 378 504 |
| surfaceType | enum - SurfaceCurveType | PRI | 'None' | |
| date | VARCHAR(10) | '' | ||
| time | VARCHAR(8) | '' | ||
| sDiv | FLOAT | 0 | Interpolated implied sdiv rate from LiveSurfaceFixedTermsDivN | |
| fwdUPrc | FLOAT | 0 | Interpolated implied forward price from LiveSurfaceFixedTermfwdUPrcN | |
| eCnt | INT | 0 | Number of expected earnings dates from LiveSurfaceFixedTermeCntN | |
| eMove | FLOAT | 0 | Implied earnings move from LiveSurfaceFixedTermeMove | |
| eMoveHist | FLOAT | 0 | Historical earnings move from LiveSurfaceFixedTermeMoveHist | |
| volD45 | FLOAT | 0 | xde 45 censored volatility | |
| volD40 | FLOAT | 0 | xde 40 | |
| volD35 | FLOAT | 0 | xde 35 | |
| volD30 | FLOAT | 0 | xde 30 | |
| volD25 | FLOAT | 0 | xde 25 | |
| volD20 | FLOAT | 0 | xde 20 | |
| volD15 | FLOAT | 0 | xde 15 | |
| volD10 | FLOAT | 0 | xde 10 | |
| volD05 | FLOAT | 0 | xde 5 | |
| volA00 | FLOAT | 0 | xde 0 | |
| volU05 | FLOAT | 0 | xde 5 | |
| volU10 | FLOAT | 0 | xde 10 | |
| volU15 | FLOAT | 0 | xde 15 | |
| volU20 | FLOAT | 0 | xde 20 | |
| volU25 | FLOAT | 0 | xde 25 | |
| volU30 | FLOAT | 0 | xde 30 | |
| volU35 | FLOAT | 0 | xde 35 | |
| volU40 | FLOAT | 0 | xde 40 | |
| volU45 | FLOAT | 0 | xde 45 | |
| vWidth | FLOAT | 0 | atm volatility width from LiveSurfaceFixedTermvWidthN | |
| vSlope | FLOAT | 0 | atm volatility slope from LiveSurfaceFixedTermvSlopeN | |
| loYears | FLOAT | 0 | LiveSurfaceCurveyears before days 1 none | |
| hiYears | FLOAT | 0 | LiveSurfaceCurveyears after days 1 none | |
| minDelta | FLOAT | 0 | minimum valid strike delta | |
| maxDelta | FLOAT | 0 | maximum valid strike delta | |
| timestamp | DATETIME(6) | '1900-01-01 00:00:00.000000' | surface fit timestamp |
PRIMARY KEY DEFINITION (Unique)
| Field | Sequence |
|---|---|
| ticker_tk | 1 |
| ticker_at | 2 |
| ticker_ts | 3 |
| days | 4 |
| surfaceType | 5 |
CREATE TABLE EXAMPLE QUERY
CREATE TABLE `SRAnalytics`.`MsgLiveSurfaceFixedGrid` (
`ticker_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None',
`ticker_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None',
`ticker_tk` VARCHAR(12) NOT NULL DEFAULT '',
`days` SMALLINT NOT NULL DEFAULT 0 COMMENT 'days to expiration [5, 21, 42, 63, 84, 105, 126, 189, 252, 378, 504]',
`surfaceType` ENUM('None','Live','PrevDay','Interp','Close','Test') NOT NULL DEFAULT 'None',
`date` VARCHAR(10) NOT NULL DEFAULT '',
`time` VARCHAR(8) NOT NULL DEFAULT '',
`sDiv` FLOAT NOT NULL DEFAULT 0 COMMENT 'Interpolated implied sdiv rate (from LiveSurfaceFixedTerm.sDiv_N)',
`fwdUPrc` FLOAT NOT NULL DEFAULT 0 COMMENT 'Interpolated implied forward price (from LiveSurfaceFixedTerm.fwdUPrc_N)',
`eCnt` INT NOT NULL DEFAULT 0 COMMENT 'Number of expected earnings dates (from LiveSurfaceFixedTerm.eCnt_N)',
`eMove` FLOAT NOT NULL DEFAULT 0 COMMENT 'Implied earnings move (from LiveSurfaceFixedTerm.eMove)',
`eMoveHist` FLOAT NOT NULL DEFAULT 0 COMMENT 'Historical earnings move (from LiveSurfaceFixedTerm.eMoveHist)',
`volD45` FLOAT NOT NULL DEFAULT 0 COMMENT 'xde = -45 (censored volatility)',
`volD40` FLOAT NOT NULL DEFAULT 0 COMMENT 'xde = -40',
`volD35` FLOAT NOT NULL DEFAULT 0 COMMENT 'xde = -35',
`volD30` FLOAT NOT NULL DEFAULT 0 COMMENT 'xde = -30',
`volD25` FLOAT NOT NULL DEFAULT 0 COMMENT 'xde = -25',
`volD20` FLOAT NOT NULL DEFAULT 0 COMMENT 'xde = -20',
`volD15` FLOAT NOT NULL DEFAULT 0 COMMENT 'xde = -15',
`volD10` FLOAT NOT NULL DEFAULT 0 COMMENT 'xde = -10',
`volD05` FLOAT NOT NULL DEFAULT 0 COMMENT 'xde = -5',
`volA00` FLOAT NOT NULL DEFAULT 0 COMMENT 'xde = 0',
`volU05` FLOAT NOT NULL DEFAULT 0 COMMENT 'xde = +5',
`volU10` FLOAT NOT NULL DEFAULT 0 COMMENT 'xde = +10',
`volU15` FLOAT NOT NULL DEFAULT 0 COMMENT 'xde = +15',
`volU20` FLOAT NOT NULL DEFAULT 0 COMMENT 'xde = +20',
`volU25` FLOAT NOT NULL DEFAULT 0 COMMENT 'xde = +25',
`volU30` FLOAT NOT NULL DEFAULT 0 COMMENT 'xde = +30',
`volU35` FLOAT NOT NULL DEFAULT 0 COMMENT 'xde = +35',
`volU40` FLOAT NOT NULL DEFAULT 0 COMMENT 'xde = +40',
`volU45` FLOAT NOT NULL DEFAULT 0 COMMENT 'xde = +45',
`vWidth` FLOAT NOT NULL DEFAULT 0 COMMENT 'atm volatility width (from LiveSurfaceFixedTerm.vWidth_N)',
`vSlope` FLOAT NOT NULL DEFAULT 0 COMMENT 'atm volatility slope (from LiveSurfaceFixedTerm.vSlope_N)',
`loYears` FLOAT NOT NULL DEFAULT 0 COMMENT 'LiveSurfaceCurve.years before days [-1 = none]',
`hiYears` FLOAT NOT NULL DEFAULT 0 COMMENT 'LiveSurfaceCurve.years after days [-1 = none]',
`minDelta` FLOAT NOT NULL DEFAULT 0 COMMENT 'minimum valid strike delta',
`maxDelta` FLOAT NOT NULL DEFAULT 0 COMMENT 'maximum valid strike delta',
`timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'surface fit timestamp',
PRIMARY KEY USING HASH (`ticker_tk`,`ticker_at`,`ticker_ts`,`days`,`surfaceType`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='This table contains a live grided (interpolated) censored implied volatility surface. Each record contains standarized live and prior period implied volatilities at standarized skew points for a standardized days-to-expiration value.\nLiveSurfaceGrid records are published to the SpiderRock elastic cluster nightly.';
SELECT TABLE EXAMPLE QUERY
SELECT
`ticker_at`,
`ticker_ts`,
`ticker_tk`,
`days`,
`surfaceType`,
`date`,
`time`,
`sDiv`,
`fwdUPrc`,
`eCnt`,
`eMove`,
`eMoveHist`,
`volD45`,
`volD40`,
`volD35`,
`volD30`,
`volD25`,
`volD20`,
`volD15`,
`volD10`,
`volD05`,
`volA00`,
`volU05`,
`volU10`,
`volU15`,
`volU20`,
`volU25`,
`volU30`,
`volU35`,
`volU40`,
`volU45`,
`vWidth`,
`vSlope`,
`loYears`,
`hiYears`,
`minDelta`,
`maxDelta`,
`timestamp`
FROM `SRAnalytics`.`MsgLiveSurfaceFixedGrid`
WHERE
/* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */
`ticker_at` = 'None'
AND
/* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') */
`ticker_ts` = 'None'
AND
/* Replace with a VARCHAR(12) */
`ticker_tk` = 'Example_ticker_tk'
AND
/* Replace with a SMALLINT */
`days` = 0
AND
/* Replace with a ENUM('None','Live','PrevDay','Interp','Close','Test') */
`surfaceType` = 'None';
Doc Columns Query
SELECT * FROM SRAnalytics.doccolumns WHERE TABLE_NAME='LiveSurfaceFixedGrid' ORDER BY ordinal_position ASC;